package com.example.demo.controller;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.util.StringUtils;

import com.mchange.v2.c3p0.ComboPooledDataSource;


public class DBUtil {

    public static  String url ="jdbc:mysql://47.95.248.93:3306/";
    public static  String dbname ="mytest";
    
    public static  String username="root";
    
    public static  String password ="zm123456";
    
    public static  String driverclass="com.mysql.jdbc.Driver";
    
    
    private static ComboPooledDataSource comboPooledDataSource;
    private static Connection connection;
    
    public static ComboPooledDataSource getDataSource() throws PropertyVetoException  {
    	System.out.println("connet to db:"+url+" dbname:"+dbname+ " username:"+username+" password:"+password);
    	comboPooledDataSource = new ComboPooledDataSource();
    	comboPooledDataSource.setDriverClass(driverclass);
    	comboPooledDataSource.setJdbcUrl(url+dbname);
    	comboPooledDataSource.setUser(username);
    	comboPooledDataSource.setPassword(password);
    	comboPooledDataSource.setMaxPoolSize(20);
        comboPooledDataSource.setMinPoolSize(5);
        comboPooledDataSource.setInitialPoolSize(10);
        comboPooledDataSource.setMaxIdleTime(3000);
        comboPooledDataSource.setAcquireIncrement(5);
        comboPooledDataSource.setIdleConnectionTestPeriod(600);
//        comboPooledDataSource.close();
        return comboPooledDataSource;
    }
    
	public synchronized static Connection getConnection() throws PropertyVetoException {
		if (connection == null) {
			try {
				if(comboPooledDataSource == null) {
					comboPooledDataSource = DBUtil.getDataSource();
				}
				connection = comboPooledDataSource.getConnection();
				System.out.println("connection is ready!!!");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return connection;
	}
    
    public synchronized static void closeConnection() {
        try {
            
            if(connection != null) {
            	connection.close();
            	connection = null;
            	System.out.println("connection  is close!!!");
			}
            if(comboPooledDataSource != null) {
				comboPooledDataSource.close();
				comboPooledDataSource = null;
				System.out.println("dataSource  is close!!!");
			}
        } catch (SQLException e) {
            e.printStackTrace();
        } 
    }
    
    public static ResultSet executeQuery(Connection connection, String sql) {
        if (StringUtils.isEmpty(sql)) {
            System.out.println("sql语句不为空");
            return null;
        }
        ResultSet rs = null;
        PreparedStatement ps = null;
        System.out.println("sql--> " + sql);
        try {
            ps = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
            rs = ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }
}